package com.yonyou.portcity.utils;

import com.yonyou.portcity.enums.OptRetNameEnum;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    public static ArrayList<ArrayList<String>> importExcel(InputStream inputStream) {
        ArrayList<ArrayList<String>> rowList = new ArrayList<>();
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            workbook.getNumberOfSheets();
            Sheet sheet = workbook.getSheetAt(0);
            DecimalFormat decimalFormat = new DecimalFormat("#");

            Row firstRow = sheet.getRow(0);//为能读到所有单元格，包含空单元格
            for (int rowNum = 1; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
                ArrayList<String> cellList = new ArrayList<>();
                Row row = sheet.getRow(rowNum);
                for (int cellNum = 0; cellNum < firstRow.getLastCellNum(); cellNum++ ) {
                    Cell cell = row.getCell(cellNum);
                    if (cell == null) {
                        cellList.add("");
                    } else if (CellType.NUMERIC.equals(cell.getCellTypeEnum())) {
                        cellList.add(decimalFormat.format(cell.getNumericCellValue()));
                    }else if (CellType.STRING.equals(cell.getCellTypeEnum())) {
                        cellList.add(cell.getStringCellValue());
                    } else {
                        cell.setCellType(CellType.STRING);
                        cellList.add(cell.getStringCellValue());
                    }
                }
                rowList.add(cellList);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return rowList;
    }
    public static  Map<String,List<ArrayList<String>>> importExcelReturn(InputStream inputStream) {
        Map<String,List<ArrayList<String>>> map=new HashMap();
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            for (int i=0;i<workbook.getNumberOfSheets();i++){
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet.getSheetName().equals(OptRetNameEnum.ZCFLHZ.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCZLSR.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.CLTDXX.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.ZJXMXX.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCJBXX.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCXKZXX.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCLCXX.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCJYQKY.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCJYQKE.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCJYQKS.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.FCZLHTXX.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.TDZLHTXX.getDesc())||
                        sheet.getSheetName().equals(OptRetNameEnum.KHML.getDesc())){
                    ArrayList<ArrayList<String>> rowList = new ArrayList<>();
                    DecimalFormat decimalFormat = new DecimalFormat("#");

                    Row firstRow = sheet.getRow(0);//为能读到所有单元格，包含空单元格
                    for (int rowNum = 1; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
                        ArrayList<String> cellList = new ArrayList<>();
                        Row row = sheet.getRow(rowNum);
                        //判断整行是否都为空数据，如果全部是空数据全部跳出
                            if (isRowEmpty(row)){
                               break;
                            }
                            for (int cellNum = 0; cellNum < firstRow.getLastCellNum(); cellNum++ ) {
                                Cell cell = row.getCell(cellNum);
                                if (cell == null) {
                                    cellList.add("");
                                }else if (CellType.NUMERIC.equals(cell.getCellTypeEnum())) {
                                    cellList.add(decimalFormat.format(cell.getNumericCellValue()));
                                }else if (CellType.STRING.equals(cell.getCellTypeEnum())) {
                                    cellList.add(cell.getStringCellValue());
                                } else {
                                    cell.setCellType(CellType.STRING);
                                    cellList.add(cell.getStringCellValue());
                                }
                        }
                        if (cellList.contains("填表说明：")||cellList.contains("填报说明：")||cellList.contains("填写说明：")){
                            break;
                        }
                        rowList.add(cellList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.ZCFLHZ.getDesc())){
                        map.put("ZCFLHZ",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCZLSR.getDesc())){
                        map.put("FCZLSR",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.CLTDXX.getDesc())){
                        map.put("CLTDXX",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.ZJXMXX.getDesc())){
                        map.put("ZJXMXX",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCJBXX.getDesc())){
                        map.put("FCJBXX",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCXKZXX.getDesc())){
                        map.put("FCXKZXX",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCLCXX.getDesc())){
                        map.put("FCLCXX",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCJYQKY.getDesc())){
                        map.put("FCJYQKY",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCJYQKE.getDesc())){
                        map.put("FCJYQKE",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCJYQKS.getDesc())){
                        map.put("FCJYQKS",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.FCZLHTXX.getDesc())){
                        map.put("FCZLHTXX",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.TDZLHTXX.getDesc())){
                        map.put("TDZLHTXX",rowList);
                    }
                    if (sheet.getSheetName().equals(OptRetNameEnum.KHML.getDesc())){
                        map.put("KHML",rowList);
                    }
                }

            }
        } catch (IOException e) {
            e.printStackTrace();

        }

        return map;
    }
    public static boolean isRowEmpty(Row row){
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType()!=CellType.BLANK){
                return false;
            }
        }
        return true;
    }
    public static byte[] File2byte(MultipartFile file){
        byte[] buffer = null;
        try {
            InputStream fis = file.getInputStream();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            byte[] b = new byte[1024];
            int n;
            while ((n = fis.read(b)) != -1) {
                bos.write(b, 0, n);
            }
            fis.close();
            bos.close();
            buffer = bos.toByteArray();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e)  {
            e.printStackTrace();
        }
        return buffer;
    }
    
    public static void byte2File(byte[] buf, String filePath, String fileName) {
        BufferedOutputStream bos = null;
        FileOutputStream fos = null;
        File file = null;
        try {
          File dir = new File(filePath);
          /*if (!dir.exists() && dir.isDirectory()) {
            dir.mkdirs();
          }*/
          if (!dir.exists()) {
              dir.mkdirs();
            }
          file = new File(filePath + File.separator + fileName);
          fos = new FileOutputStream(file);
          bos = new BufferedOutputStream(fos);
          bos.write(buf);
        } catch (Exception e) {
          e.printStackTrace();
        } finally {
          if (bos != null) {
            try {
              bos.close();
            } catch (IOException e) {
              e.printStackTrace();
            }
          }
          if (fos != null) {
            try {
              fos.close();
            } catch (IOException e) {
              e.printStackTrace();
            }
          }
        }
      }

}
